﻿using System;
using System.Drawing;
using System.Collections;
using System.ComponentModel;
using System.Windows.Forms;
using System.Data;
using LibHuman;
namespace Human
{
	/// <summary>
	/// Summary description for frmThongKeLDTienLuong.
	/// </summary>
	public class frmThongKeLDTienLuong : System.Windows.Forms.Form
	{
		private AccessData  mDB=new AccessData();
        Language lan = new Language();
        private DataSet ds =new DataSet();
        private frmReport rpt;
        string s_bophan = "", user_dn = "";
		private System.Windows.Forms.Button butThoat;
		private System.Windows.Forms.Button butIn;
		private System.Windows.Forms.Button butXem;
		private System.Windows.Forms.ComboBox cbobNam;
		private System.Windows.Forms.ComboBox cbobThang;
		private System.Windows.Forms.Label label2;
		private System.Windows.Forms.Label label1;
		private System.ComponentModel.Container components = null;

		public frmThongKeLDTienLuong(string s_makp,string s_userdn)
		{
			InitializeComponent();
            lan.Read_Language_to_Xml(this.Name.ToString(), this);
            lan.Changelanguage_to_English(this.Name.ToString(), this);
            s_bophan = s_makp;
            user_dn = s_userdn;
		}

		protected override void Dispose( bool disposing )
		{
			if( disposing )
			{
				if(components != null)
				{
					components.Dispose();
				}
			}
			base.Dispose( disposing );
		}

		#region Windows Form Designer generated code
		/// <summary>
		/// Required method for Designer support - do not modify
		/// the contents of this method with the code editor.
		/// </summary>
		private void InitializeComponent()
		{
            System.ComponentModel.ComponentResourceManager resources = new System.ComponentModel.ComponentResourceManager(typeof(frmThongKeLDTienLuong));
            this.butThoat = new System.Windows.Forms.Button();
            this.butIn = new System.Windows.Forms.Button();
            this.butXem = new System.Windows.Forms.Button();
            this.cbobNam = new System.Windows.Forms.ComboBox();
            this.cbobThang = new System.Windows.Forms.ComboBox();
            this.label2 = new System.Windows.Forms.Label();
            this.label1 = new System.Windows.Forms.Label();
            this.SuspendLayout();
            // 
            // butThoat
            // 
            this.butThoat.Font = new System.Drawing.Font("Microsoft Sans Serif", 8.25F, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point, ((byte)(0)));
            this.butThoat.Image = global::Human.Properties.Resources.close_r;
            this.butThoat.ImageAlign = System.Drawing.ContentAlignment.MiddleLeft;
            this.butThoat.Location = new System.Drawing.Point(161, 82);
            this.butThoat.Name = "butThoat";
            this.butThoat.Size = new System.Drawing.Size(65, 28);
            this.butThoat.TabIndex = 4;
            this.butThoat.Text = "&Thoát";
            this.butThoat.TextAlign = System.Drawing.ContentAlignment.MiddleRight;
            this.butThoat.Click += new System.EventHandler(this.butThoat_Click);
            // 
            // butIn
            // 
            this.butIn.Font = new System.Drawing.Font("Microsoft Sans Serif", 8.25F, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point, ((byte)(0)));
            this.butIn.Image = ((System.Drawing.Image)(resources.GetObject("butIn.Image")));
            this.butIn.ImageAlign = System.Drawing.ContentAlignment.MiddleLeft;
            this.butIn.Location = new System.Drawing.Point(90, 82);
            this.butIn.Name = "butIn";
            this.butIn.Size = new System.Drawing.Size(65, 28);
            this.butIn.TabIndex = 3;
            this.butIn.Text = "   &In";
            this.butIn.Click += new System.EventHandler(this.butIn_Click);
            // 
            // butXem
            // 
            this.butXem.Font = new System.Drawing.Font("Microsoft Sans Serif", 8.25F, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point, ((byte)(0)));
            this.butXem.Image = ((System.Drawing.Image)(resources.GetObject("butXem.Image")));
            this.butXem.ImageAlign = System.Drawing.ContentAlignment.MiddleLeft;
            this.butXem.Location = new System.Drawing.Point(19, 82);
            this.butXem.Name = "butXem";
            this.butXem.Size = new System.Drawing.Size(65, 28);
            this.butXem.TabIndex = 2;
            this.butXem.Text = "   &Xem";
            this.butXem.Click += new System.EventHandler(this.butXem_Click);
            this.butXem.KeyDown += new System.Windows.Forms.KeyEventHandler(this.butXem_KeyDown);
            // 
            // cbobNam
            // 
            this.cbobNam.DropDownStyle = System.Windows.Forms.ComboBoxStyle.DropDownList;
            this.cbobNam.Font = new System.Drawing.Font("Microsoft Sans Serif", 9.75F, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point, ((byte)(0)));
            this.cbobNam.Location = new System.Drawing.Point(164, 34);
            this.cbobNam.Name = "cbobNam";
            this.cbobNam.Size = new System.Drawing.Size(66, 24);
            this.cbobNam.TabIndex = 1;
            this.cbobNam.KeyDown += new System.Windows.Forms.KeyEventHandler(this.cbobNam_KeyDown);
            // 
            // cbobThang
            // 
            this.cbobThang.DropDownStyle = System.Windows.Forms.ComboBoxStyle.DropDownList;
            this.cbobThang.Font = new System.Drawing.Font("Microsoft Sans Serif", 9.75F, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point, ((byte)(0)));
            this.cbobThang.Items.AddRange(new object[] {
            "01",
            "02",
            "03",
            "04",
            "05",
            "06",
            "07",
            "08",
            "09",
            "10",
            "11",
            "12"});
            this.cbobThang.Location = new System.Drawing.Point(61, 34);
            this.cbobThang.Name = "cbobThang";
            this.cbobThang.Size = new System.Drawing.Size(48, 24);
            this.cbobThang.TabIndex = 0;
            this.cbobThang.KeyDown += new System.Windows.Forms.KeyEventHandler(this.cbobThang_KeyDown);
            // 
            // label2
            // 
            this.label2.Font = new System.Drawing.Font("Microsoft Sans Serif", 9F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((byte)(0)));
            this.label2.Location = new System.Drawing.Point(121, 37);
            this.label2.Name = "label2";
            this.label2.Size = new System.Drawing.Size(40, 15);
            this.label2.TabIndex = 12;
            this.label2.Text = "Năm :";
            this.label2.TextAlign = System.Drawing.ContentAlignment.MiddleRight;
            // 
            // label1
            // 
            this.label1.Font = new System.Drawing.Font("Microsoft Sans Serif", 9F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((byte)(0)));
            this.label1.Location = new System.Drawing.Point(7, 37);
            this.label1.Name = "label1";
            this.label1.Size = new System.Drawing.Size(48, 15);
            this.label1.TabIndex = 11;
            this.label1.Text = "Tháng :";
            this.label1.TextAlign = System.Drawing.ContentAlignment.MiddleRight;
            // 
            // frmThongKeLDTienLuong
            // 
            this.AutoScaleBaseSize = new System.Drawing.Size(5, 13);
            this.ClientSize = new System.Drawing.Size(287, 153);
            this.Controls.Add(this.butThoat);
            this.Controls.Add(this.butIn);
            this.Controls.Add(this.butXem);
            this.Controls.Add(this.cbobNam);
            this.Controls.Add(this.cbobThang);
            this.Controls.Add(this.label2);
            this.Controls.Add(this.label1);
            this.ForeColor = System.Drawing.Color.DarkBlue;
            this.Icon = ((System.Drawing.Icon)(resources.GetObject("$this.Icon")));
            this.Name = "frmThongKeLDTienLuong";
            this.StartPosition = System.Windows.Forms.FormStartPosition.CenterScreen;
            this.Text = "Thống kê lao động tiền lương";
            this.Load += new System.EventHandler(this.frmThongKeLDTienLuong_Load);
            this.ResumeLayout(false);

		}
		#endregion

		private void butThoat_Click(object sender, System.EventArgs e)
		{
			this.Close();
		}

		private void frmThongKeLDTienLuong_Load(object sender, System.EventArgs e)
		{
            string m_thang = "", m_nam = "";
            m_thang = DateTime.Now.Month.ToString().PadLeft(2, '0');
            m_nam = DateTime.Now.Year.ToString();
            load_nam();
            //cbobThang.Items[
            int i = 0, y = 0;
            for (i = 0; i <= cbobThang.Items.Count - 1; i++)
            {
                if (cbobThang.Items[i].ToString() == m_thang) break;
            }
            cbobThang.SelectedIndex = i;
            for (y = 0; y <= cbobNam.Items.Count - 1; y++)
            {
                if (cbobNam.Items[y].ToString() == m_nam) break;
            }
            cbobNam.SelectedValue = y;
		}
        private void load_nam()
        {
            DataTable dt = new DataTable();
            dt.Columns.Add("nam", typeof(string));
            int nam = Int32.Parse(DateTime.Now.Year.ToString());
            int i = 0;
            DataRow nrow;
            for (i = 2008; i <= nam; i++)
            {
                nrow = dt.NewRow();
                nrow["nam"] = i.ToString();
                dt.Rows.Add(nrow);
            }
            cbobNam.DataSource = dt;
            cbobNam.DisplayMember = "nam";
            cbobNam.ValueMember = "nam";
            cbobNam.Text = nam.ToString();
        }
		private void print(bool prn)
		{
            string m_user = mDB.user;
            int nam =int.Parse(cbobNam.SelectedValue.ToString());
			string sql=" SELECT '' AS LABEL,'"+cbobThang.SelectedItem.ToString()+"' as ThangDenCT,";
            sql += " '" + cbobNam.SelectedValue.ToString() + "' as NamDenCT, bp.tenbp,bp.sothutu as stt_bo, Count(l.manv) AS TongNV";
			sql+=" ,sum(case when l.phai='Nữ' then 1 else 0 end) AS nv_Nu";
            sql+=" ,sum(case when l.phai='Nam' then 1 else 0 end) AS nv_Nam";
			sql+=" ,sum(case when m.nhombc=1 then 1 else 0 end ) AS Bien_Che";
            sql += " ,sum(case when m.nhombc <>1  then 1 else 0 end) AS Hop_Dong";
            sql += " ,sum(case when hd.dang in ('R','r') then 1 else 0 end) AS Dang_v"; 
            sql += " ,sum(case when hd.doan in ('R','r') then 1 else 0 end) AS Doan_v ";
            sql += " ,sum(case when hd.cong_doan in ('R','r') then 1 else 0 end) AS CongDoan_v";
            sql += " ,sum(case when extract('year' from age(l.ngaysinh)) >= 18 and extract('year' from age(l.ngaysinh))<=28 then 1 else 0 end ) as T18";
            sql += " ,sum(case when extract('year' from age(l.ngaysinh)) >= 29 and extract('year' from age(l.ngaysinh))<=35 then 1 else 0 end ) as T29";
            sql += " ,sum(case when extract('year' from age(l.ngaysinh)) >= 36 and extract('year' from age(l.ngaysinh))<=45 then 1 else 0 end ) as T36";
            sql += " ,sum(case when extract('year' from age(l.ngaysinh)) >= 46 and extract('year' from age(l.ngaysinh))<=55 then 1 else 0 end ) as T46";
            sql += " ,sum(case when extract('year' from age(l.ngaysinh)) >= 56 and extract('year' from age(l.ngaysinh))<=60 then 1 else 0 end ) as T56";
            sql += " ,sum(case when extract('year' from age(l.ngaysinh)) > 60 then 1 else 0 end ) as T60";
            sql += " ,sum(case when extract('year' from age(l.ngaysinh)) > 55 and l.phai='Nữ' then 1 else 0 end ) as Tnu";
            sql += " , sum(case when l.mahv in ('0008','0009') then 1 else 0 end) AS cap1";
            sql += " ,sum(case when l.mahv in ('0007','0006','0005','0004') then 1 else 0 end) AS Cap2";
            sql += " ,sum(case when l.mahv in ('0003','0002','0001') then 1 else 0 end) AS cap3";
            sql += " ,sum(case when bc.mabc in ('DH') then 1 else 0 end) AS daihoc";
            sql += " ,sum(case when bc.mabc in ('TD') then 1 else 0 end ) AS trendh";
			sql += " ,sum(hd.hs_lc) as mucluong";
            sql += " ,to_char(now(),'dd') AS ngay, to_char(now(),'mm') AS Thang, to_char(now(),'yyyy') AS nam";
            sql += " FROM " + m_user + ".hopdong hd inner join " + m_user + ".llnv l on l.manv=hd.manv ";
            sql += " left join " + m_user + ".hocvan hv on hv.mahv=l.mahv ";
            sql += " left join " + m_user + ".bangcap bc on l.td_nnghiep=bc.mabangcap";
            sql += " left join " + m_user + ".mahd m on m.mahd=hd.loaihd ";
            sql += " left join " + m_user + ".bophan bp on bp.madv=l.madv";            
            sql += " inner join " + m_user + ".max_lan ml on hd.manv=ml.manv";
            sql += " WHERE  (hd.ngaynghi is null or to_timestamp(hd.ngaynghi,'yyyy-mm-dd')>=to_timestamp('" + cbobNam.SelectedValue + "-" + cbobThang.SelectedItem + "-28','yyyy-mm-dd'))";
            if (cbobThang.SelectedIndex != -1 && cbobNam.SelectedIndex != -1)
                sql += " and to_timestamp(hd.ngaydenct,'yyyy-mm-dd')<=to_timestamp('" + cbobNam.SelectedValue + "-" + cbobThang.SelectedItem + "-28','yyyy-mm-dd')";
            sql += " and hd.lan=ml.lan";
            sql+=" GROUP BY bp.tenbp,bp.sothutu";//,'"+cbobThang.SelectedItem.ToString()+"','"+cbobNam.SelectedItem.ToString()+"'";
			sql+=" order by bp.sothutu asc";
			ds=mDB.get_data(sql);
            ds.WriteXml("..\\xml\\h_ThongKeLaoDongTL.xml", XmlWriteMode.WriteSchema);
			if (ds.Tables[0].Rows.Count>0)
			{

                rpt = new frmReport(mDB, ds,lan.Change_language_MessageText( "THỐNG KÊ LAO ĐỘNG TIỀN LƯƠNG THÁNG"), "h_ThongKeLaoDongTL.rpt", true, 0,user_dn);
					rpt.ShowDialog(this);
				
			}
			else
				MessageBox.Show(lan.Change_language_MessageText("Chưa có dữ liệu"),lan.Change_language_MessageText("Thông Báo"));
		}

		private void butXem_Click(object sender, System.EventArgs e)
		{
            try
            {
                if ((Convert.ToInt32(cbobThang.Text) > DateTime.Now.Month && Convert.ToInt32(cbobNam.Text) >= DateTime.Now.Year) || (Convert.ToInt32(cbobThang.Text) <= DateTime.Now.Month && Convert.ToInt32(cbobNam.Text) > DateTime.Now.Year))
                {
                    MessageBox.Show(lan.Change_language_MessageText("Tháng lớn hơn tháng hiện tại"),lan.Change_language_MessageText( "Thông Báo"), MessageBoxButtons.OK);
                    cbobThang.Text = DateTime.Now.Month.ToString();
                    cbobThang.Focus();

                }
                else
                {
                        print(false);
                }
               
            }
            catch
            {
                    
            }
            
		}

		private void butIn_Click(object sender, System.EventArgs e)
		{
			print(true);
		}

        private void cbobThang_KeyDown(object sender, KeyEventArgs e)
        {
            if (e.KeyCode == Keys.Enter)
                SendKeys.Send("{Tab}");
        }

        private void cbobNam_KeyDown(object sender, KeyEventArgs e)
        {
            if (e.KeyCode == Keys.Enter)
                SendKeys.Send("{Tab}");
        }

        private void butXem_KeyDown(object sender, KeyEventArgs e)
        {
            if (e.KeyCode == Keys.Enter)
                SendKeys.Send("{Tab}");
        }

	}
}
